package net.servir.capacidades.web.controller;

import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.sql.DataSource;

import net.servir.capacidades.dao.jdbc.JdbcBancoPreguntaDAO;
import net.servir.capacidades.dao.jdbc.JdbcMantenimientoDAO;
import net.servir.capacidades.model.ArchivoBancoPregunta;
import net.servir.capacidades.model.TmpLoadPregunta;
import net.servir.capacidades.service.BancoPreguntaService;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.context.WebApplicationContext;
import org.springframework.web.context.support.WebApplicationContextUtils;

//import sun.security.util.Debug;

@Component
public class ServletLoadPregunta extends HttpServlet {

	private static final long serialVersionUID = 1L;

	@Autowired
	private BancoPreguntaService bancoPreguntaService;
	
	protected void processRequest(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {

		String accion = null;
		String msg = null;

		WebApplicationContext springContext = WebApplicationContextUtils
				.getWebApplicationContext(getServletContext());

		DataSource ds1 = (DataSource) springContext.getBean("dataSource");
		JdbcMantenimientoDAO jdbcMantenimientoDAO = new JdbcMantenimientoDAO(
				ds1);

		ArchivoBancoPregunta bf = new ArchivoBancoPregunta();
		//
		
		//
		HttpSession session = request.getSession();
				

		boolean esMultipart = ServletFileUpload.isMultipartContent(request);
		String fileName = "";
		String contentType = "";
		String idDocumento = "";
		String extension = "";
		Number idBancoPregunta = null;
		InputStream fileInputStream = null;
		
		// para INS y UPD
		if (esMultipart) {
			List list = recogeParam(request);
			// verificar if (list != null)
			Iterator iter = list.iterator();
			while (iter.hasNext()) {
				FileItem item = (FileItem) iter.next();
				String name = item.getFieldName();

				if (name.compareTo("accion") == 0) {
					accion = item.getString();
				} else if (name.compareTo("id_banco_pregunta") == 0) {
					
					if (item.getString() != null && !item.getString().equals(""))
						idBancoPregunta = Integer.parseInt(item.getString());
					
					bf.setId_banco_pregunta(idBancoPregunta);
				} else if (name.compareTo("archivo") == 0) {
					//try {
						fileName = item.getName();
						contentType = item.getContentType();
						fileInputStream = item.getInputStream();
						byte[] file = item.get();
						bf.setFile(file);
					//} catch (Exception ex) {
						
					//}
				}
			}
			// para DEL
		} 
		
		if (fileName == "")
		{
			msg = "Debe seleccionar el documento";
		}
		else if ( bf.getFile() == null || bf.getFile().length == 0 )
		{
			msg = "No se ha podido leer el archivo, si persiste el error comuniquese con el administrador";
		}
		else
		{
			if (accion == null) {
				msg = "Ingreso no autorizado";
			} else if (accion.compareTo("INS") == 0) {	
				//Verificamos las extensiones permitidas
				if (!"XLS".contains(extension.toUpperCase()))
					msg = "Sólo estan permitidos los archivos de Excel (*.XLS)";
				else
				{		
					procesarCargaArchivoPreguntas(fileInputStream,session.getId(),idBancoPregunta);
					//jdbcMantenimientoDAO.RegistrarDocumento(bf);
					msg = "El archivo fue cargado";
					
				}
			} else {
				msg = "Solicitud no reconocida";
			}		
		}
		/*
		 * if (msg == null) { msg = "Solicitud atendida"; }
		 */
		//request.setAttribute("msg", msg);
		if (msg == null)
			msg = "";
		
		response.sendRedirect("uploadBancoPregunta.htm?id_banco_pregunta=" + idBancoPregunta + "&msg=" + msg);
	}

	private void procesarCargaArchivoPreguntas(InputStream input,String codSession,Number idBancoPregunta) throws IOException
	{
		HSSFSheet xlsSheet;
		HSSFWorkbook xlsWorkbook;
		HSSFRow xlsRow;
		int iRow = 0;
		int iCol = 0;
		
		WebApplicationContext springContext = WebApplicationContextUtils
		.getWebApplicationContext(getServletContext());

		DataSource ds1 = (DataSource) springContext.getBean("dataSource");
		JdbcBancoPreguntaDAO jdbcBancoPreguntaDAO = new JdbcBancoPreguntaDAO(ds1);
		
		
		xlsWorkbook = new HSSFWorkbook(input);
		xlsSheet = xlsWorkbook.getSheet("preguntas");
		
		List<TmpLoadPregunta> lstPreguntas = new LinkedList<TmpLoadPregunta>(); 
		while(true)
		{
			iRow++;
			xlsRow = xlsSheet.getRow(iRow);
			if (xlsRow == null)
				break;
			
			TmpLoadPregunta obj = new TmpLoadPregunta();
			
			obj.setCod_session(codSession);
			
			iCol = 0;
			if (xlsRow.getCell(iCol) != null )
				obj.setId_pregunta(getCellTextValue(xlsRow.getCell(iCol),"INTEGER"));
			
			iCol++;
			if (xlsRow.getCell(iCol) != null )
				obj.setPregunta(getCellTextValue(xlsRow.getCell(iCol),""));

			iCol++;
			if (xlsRow.getCell(iCol) != null )
				obj.setRespuesta(getCellTextValue(xlsRow.getCell(iCol),""));
			
			iCol++;
			if (xlsRow.getCell(iCol) != null )
				obj.setAlternativa_1(getCellTextValue(xlsRow.getCell(iCol),""));

			iCol++;
			if (xlsRow.getCell(iCol) != null )
				obj.setAlternativa_2(getCellTextValue(xlsRow.getCell(iCol),""));

			iCol++;
			if (xlsRow.getCell(iCol) != null )
				obj.setAlternativa_3(getCellTextValue(xlsRow.getCell(iCol),""));

			iCol++;
			if (xlsRow.getCell(iCol) != null )
				obj.setAlternativa_4(getCellTextValue(xlsRow.getCell(iCol),""));

			iCol++;
			if (xlsRow.getCell(iCol) != null )
				obj.setJustificacion(getCellTextValue(xlsRow.getCell(iCol),""));

			iCol++;
			if (xlsRow.getCell(iCol) != null )
				obj.setId_nivel_gobierno(getCellTextValue(xlsRow.getCell(iCol),"INTEGER"));
			
			iCol++;
			if (xlsRow.getCell(iCol) != null )
				obj.setId_funcion(getCellTextValue(xlsRow.getCell(iCol),"INTEGER"));
			
			iCol++;
			if (xlsRow.getCell(iCol) != null )
				obj.setId_pregunta_tipo(getCellTextValue(xlsRow.getCell(iCol),"INTEGER"));
			
			iCol++;
			if (xlsRow.getCell(iCol) != null )
				obj.setId_pregunta_servicio(getCellTextValue(xlsRow.getCell(iCol),"INTEGER"));
			
			iCol++;
			if (xlsRow.getCell(iCol) != null )
				obj.setGrupo_similitud(getCellTextValue(xlsRow.getCell(iCol),""));
			
			iCol++;
			if (xlsRow.getCell(iCol) != null )
				obj.setGrado_dificultad(getCellTextValue(xlsRow.getCell(iCol),"INTEGER"));
			
			iCol++;
			if (xlsRow.getCell(iCol) != null )
				obj.setCod_orden(getCellTextValue(xlsRow.getCell(iCol),""));
			
			iCol++;
			if (xlsRow.getCell(iCol) != null )
				obj.setEstado(getCellTextValue(xlsRow.getCell(iCol),"INTEGER"));	
			
			iCol++;
			if (xlsRow.getCell(iCol) != null )
				obj.setId_sistema_etapa(getCellTextValue(xlsRow.getCell(iCol),"INTEGER"));
			
			obj.setFila_excel(iRow+1);
			
			if (obj.getPregunta() == null)
				break;
			
			lstPreguntas.add(obj);			
		}
		//Cargamos los datos a la tabla temporal
//		System.out.println("eliminarPreguntasTmp");
		jdbcBancoPreguntaDAO.eliminarPreguntasTmp(codSession);
//		System.out.println("registrarPreguntasTmp");
		jdbcBancoPreguntaDAO.registrarPreguntasTmp(lstPreguntas);
//		System.out.println("validarImportacionPreguntas");
		jdbcBancoPreguntaDAO.validarImportacionPreguntas(codSession,idBancoPregunta);
	}
	
	
	private String getCellTextValue(HSSFCell cell,String type)
	{
		String strValue = "";
		
		if (cell.getCellType() == cell.CELL_TYPE_BLANK)
			strValue = "";
		else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC)
			if (type.equals("INTEGER"))
				strValue = Integer.toString((int)cell.getNumericCellValue());
			else
				strValue = Double.toString(cell.getNumericCellValue());
		
		else if (cell.getCellType() == cell.CELL_TYPE_STRING)
			strValue = cell.getRichStringCellValue().getString();
		else 
			strValue = null;

		return strValue;
			
	}
	
	private List recogeParam(HttpServletRequest request) {
		List list = null;

		try {
			FileItemFactory factory = new DiskFileItemFactory();
			ServletFileUpload upload = new ServletFileUpload(factory);
			//upload.setHeaderEncoding("UTF-8");
			list = upload.parseRequest(request);
		} catch (FileUploadException ex) {
		}

		return list;
	}

	// <editor-fold defaultstate="collapsed"
	// desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
	/**
	 * Handles the HTTP <code>GET</code> method.
	 * 
	 * @param request
	 *            servlet request
	 * @param response
	 *            servlet response
	 * @throws ServletException
	 *             if a servlet-specific error occurs
	 * @throws IOException
	 *             if an I/O error occurs
	 */
	@Override
	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		processRequest(request, response);
	}

	/**
	 * Handles the HTTP <code>POST</code> method.
	 * 
	 * @param request
	 *            servlet request
	 * @param response
	 *            servlet response
	 * @throws ServletException
	 *             if a servlet-specific error occurs
	 * @throws IOException
	 *             if an I/O error occurs
	 */
	@Override
	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		processRequest(request, response);
	}

	/**
	 * Returns a short description of the servlet.
	 * 
	 * @return a String containing servlet description
	 */
	@Override
	public String getServletInfo() {
		return "Short description";
	}// </editor-fold>
	
	/**
	 * This class assumes that the string used to initialize fullPath has a
	 * directory path, filename, and extension. The methods won't work if it
	 * doesn't.
	 */
	class FilenameTool {
	  private String fullPath;
	  private char pathSeparator, extensionSeparator;

	  public FilenameTool(String str, char sep, char ext) {
	    fullPath = str;
	    pathSeparator = sep;
	    extensionSeparator = ext;
	  }

	  public String extension() {
	    int dot = fullPath.lastIndexOf(extensionSeparator);
	    return fullPath.substring(dot + 1);
	  }

	  public String filename() { // gets filename without extension
	    int dot = fullPath.lastIndexOf(extensionSeparator);
	    int sep = fullPath.lastIndexOf(pathSeparator);
	    return fullPath.substring(sep + 1, dot);
	  }

	  public String path() {
	    int sep = fullPath.lastIndexOf(pathSeparator);
	    return fullPath.substring(0, sep);
	  }
	}
	
}
